import requests  # 数据请求模块
import parsel  # 数据解析模块
import csv
import time
import pymysql
from timeit import default_timer

f = open('lianjia.csv', mode='a', encoding='utf-8', newline='')
csv_writer = csv.DictWriter(f, fieldnames=[
    '序号',
    '标题',
    '户型',
    '建筑面积',
    '朝向',
    '装修',
    '总楼层',
    '建筑类型',
    '小区',
    '地址',
    '建筑时间',
    '价格（万）',
])

csv_writer.writeheader()

host = '150.158.80.216'
port = 3306
db = 'shop'
user = 'shop'
password = 'shop'


# ---- 用pymysql 操作数据库
def get_connection():
    conn = pymysql.connect(host=host, port=port, db=db, user=user, password=password)
    return conn


# ---- 使用 with 的方式来优化代码
class UsingMysql(object):
    def __init__(self, commit=True, log_time=True, log_label='总用时'):
        """
        :param commit: 是否在最后提交事务(设置为False的时候方便单元测试)
        :param log_time:  是否打印程序运行总时间
        :param log_label:  自定义log的文字
        """
        self._log_time = log_time
        self._commit = commit
        self._log_label = log_label

    def __enter__(self):

        # 如果需要记录时间
        if self._log_time is True:
            self._start = default_timer()

        # 在进入的时候自动获取连接和cursor
        conn = get_connection()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        conn.autocommit = False

        self._conn = conn
        self._cursor = cursor
        return self

    def __exit__(self, *exc_info):
        # 提交事务
        if self._commit:
            self._conn.commit()
        # 在退出的时候自动关闭连接和cursor
        self._cursor.close()
        self._conn.close()

        if self._log_time is True:
            diff = default_timer() - self._start
            print('-- %s: %.6f 秒' % (self._log_label, diff))

    @property
    def cursor(self):
        return self._cursor


def check_it():
    count = 0
    with UsingMysql(log_time=True) as um:
        um.cursor.execute("select count(id) as total from test")
        data = um.cursor.fetchone()
        print("-- 当前数量: %d " % data['total'])
        for page in range(1, 21):
            time.sleep(1)
            url = 'https://cq.lianjia.com/ershoufang/banan/pg{page}'

            # headers请求头:作用把python代码进行伪装，伪装成浏览器
            # User-Agent浏览器的基本信息可以直接在开发者工具里面进行复制
            headers = {
                'user-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/96.0.4664.93 Safari/537.36'
            }

            # requests.get(url=url, headers=headers) get请求方式，请求方式同样也是可以在开发者工具里面查询的
            # #response.text获取响应体的文本数据
            response = requests.get(url=url, headers=headers)
            # print(response.text)

            # 数据解析re css选择器xpath
            # parsel数据解析模块
            selector = parsel.Selector(response.text)

            lis = selector.css('.sellListContent li')
            for li in lis:
                title = li.css('.title a::text').get()  # 标题
                if title:
                    try:
                        area_list = li.css('.flood a::text').getall()
                        area = '-'.join(area_list)
                        # print(area)
                        community = area.split('-')[0]  # 小区
                        # print(community)
                        address = area.split('-')[-1]  # 地址
                        # print(address)
                        house_info = li.css('.houseInfo::text').get().split('|')
                        # print(house_info)
                        # if len(house_info) < 7:
                        #     continue
                        unit_type = house_info[0]  # 户型
                        acreage = house_info[1]  # 面积
                        path = house_info[2]  # 朝向
                        furnish = house_info[3]  # 装修
                        floor = house_info[4]  # 楼层
                        buildtime = house_info[5]  # 建筑时间
                        house_type = house_info[6]  # 建筑结构
                        follow_info = li.css('.followInfo::text').get().split('/')  # 关注人数
                        follow_man = follow_info[0]  # 关注人数
                        update_time = follow_info[1]  # 发布时间
                        tag_list = li.css('.tag span::text').getall()  # 标签
                        tag = '-'.join(tag_list)
                        total_price = li.css('.totalPrice span::text').get() + '万'  # 总价
                        unit_price = li.css('.unitPrice span::text').get().replace('单价', '')  # 单价
                        count += 1

                        dict = {
                            '序号': count,
                            '标题': title,
                            '户型': unit_type,
                            '建筑面积': acreage,
                            '朝向': path,
                            '装修': furnish,
                            '总楼层': floor,
                            '建筑类型': house_type,
                            '小区': community,
                            '地址': address,
                            '建筑时间': buildtime,
                            '价格（万）': total_price,
                        }
                        csv_writer.writerow(dict)

                        insert_sql = "insert into test (value) values (%s)"
                        um.cursor.execute(insert_sql, community + buildtime + total_price)
                        # print(count, title, area, unit_type, acreage, path, furnish, floor, buildtime, follow_man, update_time,house_type, tag, total_price, unit_price, sep=' |')
                    except Exception as e:
                        print("出现如下异常%s" % e)
                        pass


if __name__ == '__main__':
    check_it()
